package com.tracker.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import com.tracker.bean.Device;

public class DeviceDAO {

	private Connection conn = null;
	private ResultSet rs = null;
	private Statement stat = null;

	public Set<Device> getListObject(Device device) throws Exception {
		Set<Device> list = new HashSet();
		conn = DBSession.getIstance().getConnection();
		String sql = " select t04.id as id, login, imei, model, datetime, userid, visible, note, descr " +
					 " from t04_devices t04, t02_accountuser t02 " +
					 " where t02.id = t04.userid ";
		if(!device.getLogin().equals("")){
			   sql += "	 and t02.login = '" + device.getLogin() + "'";
		}

		stat = conn.createStatement();
		rs = stat.executeQuery(sql);
		while (rs.next()) 
			list.add(getObjectValue(rs));

		rs.close();
		stat.close();
		
		return list;
	}

	public Device getObjectByKey(String key) throws Exception {
		conn = DBSession.getIstance().getConnection();
		String sql = " select t04.id as id, login, imei, model, datetime, userid, visible, note from t04_devices t04, t02_accountuser t02 where t02.id = t04.userid and t04.imei = '" + key + "'";

		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery(sql);
		rs.next();
		
		return getObjectValue(rs);
	}

	private Device getObjectValue(ResultSet rs) throws Exception{
		Device device = new Device();
		device.setId(rs.getLong("id"));
		device.setImei(rs.getString("imei"));
		device.setModel(rs.getString("model"));
		device.setDatetime(rs.getString("datetime"));
		device.setUserId(rs.getLong("userid"));
		device.setVisible(((rs.getInt("visible")==0)? false : true));
		device.setLogin(rs.getString("login"));
		device.setNote(rs.getString("note"));
		device.setDescr(rs.getString("descr"));
		return device;
	}
	
	public void delete(String key) throws Exception {
		conn = DBSession.getIstance().getConnection();
		String sql = "delete from t04_devices where imei = '"+ key +"'";
		
		stat = conn.createStatement();
		stat.execute(sql);

		stat.close();
	}

	
	public void persist(Device device) throws Exception {

		conn = DBSession.getIstance().getConnection();
		String sql = "insert into t04_devices (imei, model, datetime, userid, visible, note) values (?,?,?,?,?,?)";

		if (exist(device)) {
			System.out.println(">>>>>>> LOG: device.getId() = " + device.getId());

			sql = "update t04_devices set " + 
				  "		imei = ?, " +
				  " 	model = ?, " +
				  " 	datetime = ?, " +
				  " 	userid = ?, " +
				  " 	visible = ?, " +
				  " 	note = ?, " +
				  " 	descr = ? " +
				  " where " +
				  "		id = " + String.valueOf(device.getId());		
		}
		
		PreparedStatement stat = conn.prepareStatement(sql);
		stat.setString(1, device.getImei());
		stat.setString(2, device.getModel());
		stat.setString(3, device.getDatetime());
		stat.setLong(4, device.getUserId());
		stat.setBoolean(5, device.getVisible());
		stat.setString(6, device.getNote());
		stat.setString(7, device.getDescr());
		stat.execute();

	}

	public boolean exist(Device device) {
		boolean exist = false;
		conn = DBSession.getIstance().getConnection();
		try {
			String sql = "select id from t04_devices where imei = '" + device.getImei() + "'";

			stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			if (rs.next()) {
				device.setId(rs.getLong("id"));
				exist = true;
			}
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				rs.close();
				stat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return exist;
	}

}
